﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Windows.Forms;
using System.Data;
using System.Collections;

namespace WHW_NetUtility
{
    class ExcelOperate
    {
        //IRow xRow;
        DateTime dtime;
        /// <summary>
        /// 从本地读取excel文档到DataTable中
        /// </summary>
        /// <param name="pathExcel">excel本地路径</param>
        /// <returns>包含excel内容的DataTable</returns>
        public DataTable ReadExcel(string pathExcel)
        {
            try
            {
                int excelTypeFlag = 0;
                IWorkbook excelbook = null;  //新建IWorkbook对象
                using (FileStream excelFileStream = new FileStream(pathExcel, FileMode.Open, FileAccess.Read))
                {
                    if (pathExcel.IndexOf(".xlsx") > 0)  //如果是xlsx类型的文档
                    {
                        excelbook = new XSSFWorkbook(excelFileStream);
                        excelTypeFlag = 1;
                    }
                    if ((pathExcel.IndexOf(".xlsx") <= 0)&& (pathExcel.IndexOf(".xls") > 0)) //如果是xls文档
                    {
                        try
                        {
                            excelbook = new HSSFWorkbook(excelFileStream); //如果直接将xlsx文档改成xls文档，会报错
                            excelTypeFlag = 2;
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("请不要将.xlsx的文档直接改成.xls");
                        }       
                    }
                    if ((pathExcel.IndexOf(".xls") <= 0) && (pathExcel.IndexOf(".xlsx") <= 0)) //如果不包括excel格式的文档
                    {
                        MessageBox.Show("路径错误，请选择excel格式的文档");
                        excelTypeFlag = 0;
                        return null;
                    }
                    ISheet excelSheet = excelbook.GetSheetAt(0);  //选择第一张表
                    IEnumerator rows = excelSheet.GetRowEnumerator(); //获取表的所有行
                    DataTable dtExcel = new DataTable();
                    for (int j = 0; j < (excelSheet.GetRow(0).LastCellNum); j++)
                    {
                        string columnValue=excelSheet.GetRow(0).GetCell(j).StringCellValue;//获取第0行的第j列的单元格内容
                        dtExcel.Columns.Add(columnValue); //将第一行的列内容取出来，用作表头（列名）
                    }
                    //while (rows.MoveNext())
                    //{
                        int rowNumber = excelSheet.LastRowNum;
                        for (int i = 1; i < rowNumber+1; i++)
                        {                            
                            IRow currentRow = excelSheet.GetRow(i);
                            if (currentRow==null) continue;
                            DataRow dRow = dtExcel.NewRow();
                            for (int j = 0; j < currentRow.LastCellNum; j++)
                            {
                                ICell excelCell = currentRow.GetCell(j);
                                if (excelCell == null)
                                {
                                    dRow[j] = null;
                                }
                                else
                                {
                                    dRow[j] = excelCell.ToString();
                                }
                            }
                            dtExcel.Rows.Add(dRow);
                       }
                        
                        //if (excelTypeFlag==1)
                        //{
                        //     xRow = (XSSFRow)rows.Current;    
                        //}
                        //else
                        //{
                        //     xRow = (HSSFRow)rows.Current;
                        //}
                        
                    //}
                    return dtExcel;
                }                                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
           
        }

        public int WriteExcel(string pathExcel,DataTable dt,string tableName,bool isHeaderColumn)
        {
            dtime = DateTime.Now;
            try
            {
                int excelTypeFlag = 0;
                IWorkbook excelbook = null;  //新建IWorkbook对象
                ISheet excelSheet = null;
                int count = 0;
                using (FileStream excelFileStream = new FileStream(pathExcel, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    if (pathExcel.IndexOf(".xlsx") > 0)  //如果是xlsx类型的文档
                    {
                        excelbook = new XSSFWorkbook();
                        excelTypeFlag = 1;
                    }
                    if ((pathExcel.IndexOf(".xlsx") <= 0) && (pathExcel.IndexOf(".xls") > 0)) //如果是xls文档
                    {
                        try
                        {
                            excelbook = new HSSFWorkbook(); //如果直接将xlsx文档改成xls文档，会报错
                            excelTypeFlag = 2;
                        }
                        catch (Exception)
                        {
                            MessageBox.Show("请不要将.xlsx的文档直接改成.xls");
                            return -1;
                        }
                    }
                    if ((pathExcel.IndexOf(".xls") <= 0) && (pathExcel.IndexOf(".xlsx") <= 0)) //如果不包括excel格式的文档
                    {
                        MessageBox.Show("路径错误，请选择excel格式的文档");
                        excelTypeFlag = 0;
                        return -1;
                    }
                    //创建表名
                    if (excelbook!=null)
                    {
                        if (excelbook.GetSheetIndex(tableName)==-1)
                        {
                            excelSheet = excelbook.CreateSheet(tableName);//如果表不存在，新建
                            /*excelbook.CreateSheet(tableName); */ //创建表名
                        }
                    }
                    else
                    {
                        return -1;
                    }
                    //是否将列名写入
                    if (isHeaderColumn==true)
                    {
                        IRow row = excelSheet.CreateRow(0);
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (i<dt.Columns.Count)
                            {
                                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                            }
                           
                            //if (i==dt.Columns.Count)
                            //{
                            //    row.CreateCell(i).SetCellValue("日期 时间");
                            //}
                        }
                        count = 1;
                    }
                    else
                    {
                        count = 0;
                    }
                    //添加内容
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow row = excelSheet.CreateRow(count);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            
                            
                            string m = dt.Rows[i][j].ToString();
                            row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                            if (i == 0)
                            {
                                excelSheet.AutoSizeColumn(j);
                            }
                        }
                        count++;
                    }
                    excelbook.Write(excelFileStream);
                    return count;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return -1;
            }
        }
    }
}
